package com.aiflow.Action;

import global.Constants;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;

import com.aiflow.db.ConnDB;
import com.aiflow.util.DownExcell;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


//  告警查询 
public class HeraldRecordAction {

	
	
	// 查询 告警信息
	public void findRecord() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));

	 
		String region = (String) req.getParameter("region");
		String sendStatus = (String) req.getParameter("sendStatus");
		int end = start + limit;
		int begin = start + 1;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if(region != null && !region.equals("")){
		   if(region.equals(Constants.netManager)){
			   whereSql += " and   Region_name = '' ";
		   }else{
			whereSql += " and   Region_name like '%"+region+"%' ";
		   }
		}
		if(sendStatus != null && !sendStatus.equals("")){
			whereSql += " and  send = '"+sendStatus+"' ";
		}
		
		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;

		try {
			String sql = "select * from "
					+ "("
					+ "select id,Alarm_id,case when Region_name = '' then '网管中心' else Region_name end as Region_name,Alarm_pro,Dn,Dn_type,Alarm_time,Org_severity,Location,Alarm_title, case alarm_status when 0 then '已恢复' else '未恢复' end as alarm_status, send, "+
					  " (row_number() over(ORDER BY id desc)) rb ,snumbe "+
					  " from Alarm_info  where alarm_status =1 "+whereSql+") tt "
					+ "where tt.rb between " + begin + " and " + end;

			sqlCount = "select count(1) "+
					  " from Alarm_info where alarm_status =1 "+whereSql;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("Alarm_id", rs.getString(2));
				mapdata.put("Alarm_time", rs.getString(7));
				mapdata.put("Region_name", rs.getString(3));
				mapdata.put("Alarm_pro", rs.getString(4));
				mapdata.put("Dn", rs.getString(5));
				mapdata.put("Dn_type", rs.getString(6));
				mapdata.put("Alarm_title", rs.getString(10));
				mapdata.put("alarm_status", rs.getString(11));
				mapdata.put("send", rs.getString(12));
				mapdata.put("snumbe", rs.getString(14));
 

				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();

			}
		}

	}
	
	
	
	
	public void saveRecord(){
		ConnDB db = new ConnDB();
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		String sql = " update Alarm_info set send =? where Alarm_id=?";
		

		HttpServletRequest req = ServletActionContext.getRequest();
		String arr = req.getParameter("jsonArr");
		JSONArray jsonArray = JSONArray.fromObject(arr);
		
		try {
			ps = con.prepareStatement(sql);
			for(int i=0;i<jsonArray.size();i++){
				JSONObject obj = jsonArray.getJSONObject(i);
				String send = obj.getString("send");
				String alarm_id = obj.getString("Alarm_id");
				 ps.setString(1, send);
				 ps.setString(2, alarm_id);
				 ps.addBatch();
			}
			int[] count = ps.executeBatch();
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeStatement(ps);
			db.closeConnect(con);
			
		}
		 
	}
	
	
	
	  //删除 字典表数据
	  public void deleteRecord(){
			ConnDB db = new ConnDB();
			Statement ps = null;
			HttpServletRequest req = ServletActionContext.getRequest();
			String id = req.getParameter("alarmIds");

			String sqld = "";
			id = id.substring(0, id.length() - 1);
			Connection con = db.getConnection();
			try {
				con.setAutoCommit(false);
				ps = con.createStatement();

				boolean bool = false;

				String sql = "delete Alarm_info  where Alarm_id in( " + id + " )";
				bool = ps.execute(sql);
				ps = con.createStatement();
				if (bool) {
					//System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (Exception e) {
				e.printStackTrace();
				try {
					con.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			} finally {
				try {
					con.commit();
					ps.close();
					con.close();
				} catch (Exception ex1) {
					ex1.printStackTrace();
				}
			}
		  }
	
	  
	  public void down(){
			HttpServletRequest req = ServletActionContext.getRequest();
			HttpServletResponse res = ServletActionContext.getResponse();
			String region = (String) req.getParameter("region");
			String sendStatus = (String) req.getParameter("sendStatus");
			ConnDB db = new ConnDB();
			ResultSet rs = null;
			JSONObject json = new JSONObject();
			List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
			JSONArray datas = new JSONArray();
			String whereSql = "";
			if(region != null && !region.equals("")){
			   if(region.equals(Constants.netManager)){
				   whereSql += " and   Region_name = '' ";
			   }else{
				whereSql += " and   Region_name like '%"+region+"%' ";
			   }
			}
			if(sendStatus != null && !sendStatus.equals("")){
				whereSql += " and  send = '"+sendStatus+"' ";
			}
			
			String sqlCount = "";
			Connection con = db.getConnection();
			PreparedStatement ps = null;

				String sql = "select Alarm_id 告警编号 ,case when Region_name = '' then '网管中心' else Region_name end as 区分公司,Alarm_pro as  告警专业,Dn 网元名称,Dn_type 网元名称,Alarm_time 告警时间,Alarm_title 告警标题, case alarm_status when 0 then '已恢复' else '未恢复' end as  告警状态, case send when 0 then '未发送' when 1 then '已发送' end as 发送状态 "+
						  " from Alarm_info  where 1=1 "+whereSql   ;
	   
				DownExcell excel = new DownExcell();
				excel.getData(sql, req, res,"传报记录核查");
  	  
	  }
	  
	  
	  
	  public void sendCount(){
		  HttpServletRequest req = ServletActionContext.getRequest();
		  HttpServletResponse resp = ServletActionContext.getResponse();
		  String alarmId = req.getParameter("alarmId");
		  ConnDB db = new ConnDB();
		  Connection con = db.getConnection();
			PreparedStatement ps = null;
			ResultSet resu = null;
            List<Map<String,String>> list = new ArrayList<Map<String,String>>();
			try {
				String sql = "select d.name,count(d.name) from dbo.t_s_alarmRecord r, dbo.dictionary d "+
                  " where alarmId = ? and r.alarmTargetId = d.id "+
                  " group by d.name ";
				ps = con.prepareStatement(sql);
				ps.setString(1,alarmId);
				resu = ps.executeQuery();
                 String nam = "";
                 String coun = "";
				while (resu.next()) {
					
				   Map map = new HashMap<String,String>();
					nam = resu.getString(1);
					coun = resu.getString(2);
					map.put("name", nam);
					map.put("count", coun);
					
					list.add(map);
				}
				JSONObject json = new JSONObject();
				 //{"success":true,"inMoney":500,"outMoney":334,"balance":166,"currentInMoney":500,"currentOutMoney":334}
				JSONArray datas = JSONArray.fromObject(list);
				// JSONObject datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
			// 	json.put("results", index);
		  resp.setCharacterEncoding("utf-8");
		   
			PrintWriter pri = resp.getWriter();
			pri.print(json);
			pri.close();
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeResultSet(resu);
			db.closeStatement(ps);
			db.closeConnect(con);
			
		}
	  }
	  
	  
	  public void sendCountSupervise(){
		  HttpServletRequest req = ServletActionContext.getRequest();
		  HttpServletResponse resp = ServletActionContext.getResponse();
		  String superviseid = req.getParameter("superviseid");
		  ConnDB db = new ConnDB();
		  Connection con = db.getConnection();
			PreparedStatement ps = null;
			ResultSet resu = null;
            List<Map<String,String>> list = new ArrayList<Map<String,String>>();
			try {
				String sql = "select d.sort,d.name,count(d.name) from dbo.t_s_superviseRecord r, dbo.dictionary d "+
                  " where superviseId like ? and r.superviseTargetId = d.id "+
                  " group by d.name,d.sort order by d.sort";
				ps = con.prepareStatement(sql);
				ps.setString(1,"%"+superviseid+"%");
				resu = ps.executeQuery();
                String nam = "";
                String coun = "";
				while (resu.next()) {
				    Map<String,String> map = new HashMap<String,String>();
					nam = resu.getString(2);
					coun = resu.getString(3);
					map.put("name", nam);
					map.put("count", coun);
					list.add(map);
				}
				JSONObject json = new JSONObject();
				JSONArray datas = JSONArray.fromObject(list);
				json.put("success", true);
				json.put("datas", datas);
		    resp.setCharacterEncoding("utf-8");
			PrintWriter pri = resp.getWriter();
			pri.print(json);
			pri.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeResultSet(resu);
			db.closeStatement(ps);
			db.closeConnect(con);
		}
	  }
	  
	  
	  
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
